package clases.pe.edu;

import pe.edu.ulima.bean.Proveedor;
import pe.edu.ulima.bean.Cliente;
import pe.edu.ulima.bean.Detalle;
import pe.edu.ulima.bean.Empleado;
import pe.edu.ulima.bean.Producto;
import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.text.SimpleDateFormat;
import java.util.ArrayList;
import java.util.Date;
import java.util.List;
import java.util.logging.Level;
import java.util.logging.Logger;
import pe.edu.ulima.bean.Almacen;
import pe.edu.ulima.bean.Boleta;
import pe.edu.ulima.bean.Comprobante;

public class CarniceriaDAO {
    
    
    DBConection dbc = new DBConection();
    Connection con =  null;
    
    
    private void ingresarCliente(Cliente cli){
        String sql = "insert into cliente values (?,?,?,?,?,?)";
        PreparedStatement ps = null;
        try {
            con = dbc.getConnection();
            ps = con.prepareStatement(sql);
            ps.setInt(1,seqCliente());
            ps.setString(2,cli.getNombre());
            ps.setString(3, cli.getApellido());
            ps.setInt(4, 0);
            ps.setString(5, cli.getDireccion());
            ps.setString(6, "");
            ps.executeUpdate();
            
                  
            
        } catch (SQLException ex) {
            Logger.getLogger(CarniceriaDAO.class.getName()).log(Level.SEVERE, null, ex);
        } finally {
            try {
                ps.close();
                con.close();
            } catch (SQLException ex) {
                Logger.getLogger(CarniceriaDAO.class.getName()).log(Level.SEVERE, null, ex);
            }
        }
    }
    
    private int seqCliente(){
        int num=0;
        PreparedStatement ps = null;
        ResultSet rs = null;
        String sql = "select seq_pruebita.nextval from dual";
        try {
            con=dbc.getConnection();
            ps = con.prepareStatement(sql);
            rs = ps.executeQuery();
            while(rs.next()){
               num=rs.getInt(1);
            }
                
            
            
        } catch (SQLException ex) {
            
        } finally {
            try {
                ps.close();
                rs.close();
                con.close();
            } catch (SQLException ex) {
                
            }
        }
        return num;
    }
    
    private int seqComprobante(){
        int num=0;
        PreparedStatement ps = null;
        ResultSet rs = null;
        String sql = "select seq_comprobante.nextval from dual";
        try {
            con=dbc.getConnection();
            ps = con.prepareStatement(sql);
            rs = ps.executeQuery();
            while(rs.next()){
               num=rs.getInt(1);
            }
                
            
            
        } catch (SQLException ex) {
            
        } finally {
            try {
                ps.close();
                rs.close();
                con.close();
            } catch (SQLException ex) {
                
            }
        }
        return num;
    }
    
    private int seqBoleta(){
        int num=0;
        PreparedStatement ps = null;
        ResultSet rs = null;
        String sql = "select seq_boleta.nextval from dual";
        try {
            con=dbc.getConnection();
            ps = con.prepareStatement(sql);
            rs = ps.executeQuery();
            while(rs.next()){
               num=rs.getInt(1);
            }
                
            
            
        } catch (SQLException ex) {
            
        } finally {
            try {
                ps.close();
                rs.close();
                con.close();
            } catch (SQLException ex) {
                
            }
        }
        return num;
    }
    
    public void ingresarComprobanteBolet(Cliente cli, List<Detalle> dets){
        String sql2 = "insert into COMPROBANTE (cod_venta,cod_cliente,tipo) values(?,?,?)";
        String sql3 = "insert into BOLETA values(?,?)";
        ingresarCliente(cli);
         int codCli = calcularCodUltimoCliente();
          PreparedStatement ps = null;
          
          Date dt=null;
          
        try {
            con= dbc.getConnection();
            ps = con.prepareStatement(sql2);
            ps.setInt(1,seqComprobante());
            ps.setInt(2, codCli);
            ps.setString(3, "Boleta");
            ps.executeUpdate();
            
        } catch (SQLException ex) {
            Logger.getLogger(CarniceriaDAO.class.getName()).log(Level.SEVERE, null, ex);
        } finally {
            try {
                ps.close();
                con.close();
            } catch (SQLException ex) {
                Logger.getLogger(CarniceriaDAO.class.getName()).log(Level.SEVERE, null, ex);
            }
        }
        int codVent = calcularCodUltimaVenta();
        try {
            con= dbc.getConnection();
            ps = con.prepareStatement(sql3);
            ps.setInt(1,seqBoleta());
            ps.setInt(2, codVent);
            ps.executeUpdate();
            
            
        } catch (SQLException ex) {
            Logger.getLogger(CarniceriaDAO.class.getName()).log(Level.SEVERE, null, ex);
        } finally {
            try {
                ps.close();
                con.close();
            } catch (SQLException ex) {
                Logger.getLogger(CarniceriaDAO.class.getName()).log(Level.SEVERE, null, ex);
            }
        }
        
        
        ingresarDetalles(dets, codVent);
        updateVentaBol(dets);
        
    }
    
    private void ingresarDetalles(List<Detalle> detas, int codVent){
        
        String sql4 = "insert into DETALLE values(?,?,?,?)";
        
        PreparedStatement ps = null;
        
        
       
        List<Detalle> nuevosDetalles = generarImportes(detas);
        try {
            con= dbc.getConnection();
            for(int i=1; i<nuevosDetalles.size();i++){
            ps = con.prepareStatement(sql4);
            ps.setInt(1, codVent);
            ps.setInt(2, nuevosDetalles.get(i).getCod_producto());
            ps.setFloat(3, nuevosDetalles.get(i).getPeso());
            ps.setFloat(4, nuevosDetalles.get(i).getImporte());
            ps.executeUpdate();
            
            }
            
        } catch (SQLException ex) {
            Logger.getLogger(CarniceriaDAO.class.getName()).log(Level.SEVERE, null, ex);
        } finally {
            try {
                ps.close();
                con.close();
            } catch (SQLException ex) {
                Logger.getLogger(CarniceriaDAO.class.getName()).log(Level.SEVERE, null, ex);
            }
        }
        
       
        
    }
    private void updateVentaBol(List<Detalle> detas){
        String sql5 = "update COMPROBANTE set precio_total = ? where cod_venta = ?";
         float precioTotal = calcularPrecioTotal(detas);
        List<Detalle> nuevosDetalles = generarImportes(detas);
        int codVent = calcularCodUltimaVenta();
        PreparedStatement ps = null;
        try {
            con= dbc.getConnection();
            ps = con.prepareStatement(sql5);
            ps.setFloat(1,precioTotal );
            ps.setInt(2, codVent);
            ps.executeUpdate();
            
        } catch (SQLException ex) {
            Logger.getLogger(CarniceriaDAO.class.getName()).log(Level.SEVERE, null, ex);
        } finally {
            try {
                ps.close();
                con.close();
            } catch (SQLException ex) {
                Logger.getLogger(CarniceriaDAO.class.getName()).log(Level.SEVERE, null, ex);
            }
        }
        
                  
                 
         for(int i=0;i<nuevosDetalles.size();i++){
             ModificarAlm(detas.get(i).getCod_producto(),detas.get(i).getPeso(), getAlmacenDeUnProd(detas.get(i).getCod_producto()));
             
             
         }
    }
    
    private List<Detalle> generarImportes(List<Detalle> dets){
        
        PreparedStatement ps=null;
        ResultSet rs =null;
        float precio = 0.0f;
        String sql="select precio_unitario from producto where cod_producto=?";
        
        for (int i =0; i<dets.size();i++){
            try {
            con= dbc.getConnection();
            
           ps=con.prepareStatement(sql);
           ps.setInt(1, dets.get(i).getCod_producto());
           rs=ps.executeQuery();
           while(rs.next()){
               precio=rs.getFloat(1);
           }
           
           dets.get(i).setImporte(precio*dets.get(i).getPeso());
            
        } catch (SQLException ex) {
            Logger.getLogger(CarniceriaDAO.class.getName()).log(Level.SEVERE, null, ex);
        } finally {
            try {
                ps.close();
                con.close();
            } catch (SQLException ex) {
                Logger.getLogger(CarniceriaDAO.class.getName()).log(Level.SEVERE, null, ex);
            }
        }
        }
        return dets;
    }
    private List<Almacen> getAlmacenDeUnProd(int codProd){
        
        Almacen alm=null;
        List<Almacen> listaAlm=new ArrayList<>();
        ResultSet rs = null;
        PreparedStatement ps = null;
        String sql = "select * from almacen where restante>0 and fecha_caducidad>sysdate and cod_producto=? order by fecha_caducidad";
        try {
            con = dbc.getConnection();
            ps = con.prepareStatement(sql);
            ps.setInt(1,codProd);
            rs = ps.executeQuery();
            if (rs.next()){
                alm=new Almacen();
                alm.setCodigo_movimiento(rs.getInt(1));
                alm.setRestante(rs.getInt(6));
                alm.setFecha_caducidad(rs.getDate(7));
                listaAlm.add(alm);
                
           }
            
        } catch (SQLException ex) {
            Logger.getLogger(CarniceriaDAO.class.getName()).log(Level.SEVERE, null, ex);
        } finally {
            try {
                ps.close();
                rs.close();
                con.close();
            } catch (SQLException ex) {
                Logger.getLogger(CarniceriaDAO.class.getName()).log(Level.SEVERE, null, ex);
            }
        }
       return listaAlm;
    }
    
    private void ModificarAlm(int codProd, float cant,List<Almacen> lista){
       
        //cant es lo que falta por vender, o por sacar
      
        PreparedStatement ps = null;
            
        String sql = "update almacen set restante = ? where cod_movimiento=?";
         try {
            con=dbc.getConnection();
             for(int i=0;i<lista.size();i++){
                 System.out.println(i);
             ps = con.prepareStatement(sql);
             
            if(lista.get(i).getRestante()>cant ){
                //Si alcanza 
               
                lista.get(i).setRestante(lista.get(i).getRestante()-cant);
                ps.setFloat(1,lista.get(i).getRestante());
                ps.setInt(2,lista.get(i).getCodigo_movimiento());
                ps.executeUpdate();
                break;
                //Updateo y me queda un resto
            }else if (lista.get(i).getRestante()<=cant){
                //no alcanza
                cant=cant-lista.get(i).getRestante();
                lista.get(i).setRestante(0);
                ps.setFloat(1,lista.get(i).getRestante());
                ps.setInt(2,lista.get(i).getCodigo_movimiento());
                ps.executeUpdate();
                
                //Update y pongo 0
            }
            
        }
          
            
            
        } catch (SQLException ex) {
            Logger.getLogger(CarniceriaDAO.class.getName()).log(Level.SEVERE, null, ex);
        } finally {
            try {
                //ps.close();
                con.close();
            } catch (SQLException ex) {
                Logger.getLogger(CarniceriaDAO.class.getName()).log(Level.SEVERE, null, ex);
            }
        }
     }
    
    public float calcularStockProducto(int codProd){
        float sto = 0.0f;
        List<Almacen> alms = this.getAlmacenDeUnProd(codProd);
        for(int i=0;i<alms.size();i++){
            sto=sto+alms.get(i).getRestante();
        }
        return sto;
    }
   
    
    private int calcularCodUltimaVenta(){
        int cod =0;
        
        ResultSet rs = null;
        PreparedStatement ps = null;
        String sql = "select * from comprobante where rownum=1 order by cod_venta desc";
        try {
            con=dbc.getConnection();
            ps = con.prepareStatement(sql);
            rs = ps.executeQuery();
            if (rs.next()){
                cod = rs.getInt(1);
            }
            
        } catch (SQLException ex) {
            Logger.getLogger(CarniceriaDAO.class.getName()).log(Level.SEVERE, null, ex);
        } finally {
            try {
                ps.close();
                rs.close();
                con.close();
            } catch (SQLException ex) {
                Logger.getLogger(CarniceriaDAO.class.getName()).log(Level.SEVERE, null, ex);
            }
        }
        return cod;
    }
    
    private int calcularCodUltimoCliente(){
        int cod=0;
        ResultSet rs = null;
        PreparedStatement ps = null;
        String sql = "select * from cliente where rownum=1 order by cod_cliente desc";
        try {
            con=dbc.getConnection();
            ps = con.prepareStatement(sql);
            rs = ps.executeQuery();
            if (rs.next()){
                cod = rs.getInt(1);
            }
            
        } catch (SQLException ex) {
            Logger.getLogger(CarniceriaDAO.class.getName()).log(Level.SEVERE, null, ex);
        } finally {
            try {
                ps.close();
                rs.close();
                con.close();
            } catch (SQLException ex) {
                Logger.getLogger(CarniceriaDAO.class.getName()).log(Level.SEVERE, null, ex);
            }
        }
        return cod;
    }
    
    private float calcularPrecioTotal(List<Detalle> detas){
        float precioTot=0.0f;
        for (int i=1; i<detas.size(); i++){
            precioTot=precioTot+detas.get(i).getImporte();
        }
        return precioTot;
    }
    public List<Producto> getProductos(){
        List<Producto> prods = new ArrayList<Producto>();
        Producto prod = null;
        String sql = "select * from PRODUCTO";
        PreparedStatement ps = null;
            ResultSet rs = null;
               
        try { 
            con = dbc.getConnection();
            ps = con.prepareStatement(sql);
            rs = ps.executeQuery();
            while (rs.next()){
                prod = new Producto();
                prod.setCod_producto(rs.getInt(1));
                prod.setDescripcion(rs.getString(2));
                prod.setPrecio_unitario(rs.getFloat(3));
                prod.setInt_vencimiento(rs.getInt(4));
                
                prods.add(prod);                
            }            
        } catch (SQLException ex) {
            } finally {
            try {
                ps.close();
                rs.close();
                con.close();
            } catch (SQLException ex) {
                Logger.getLogger(CarniceriaDAO.class.getName()).log(Level.SEVERE, null, ex);
            }
            
        }
        return prods;
    }
    
    public List<Cliente> getClientes(){
        List<Cliente> clis = new ArrayList<>();
        Cliente cli = null;
        String sql = "select * from CLIENTE";
        PreparedStatement ps = null;
            ResultSet rs = null;
               
        try { 
            con = dbc.getConnection();
            ps = con.prepareStatement(sql);
            rs = ps.executeQuery();
            while (rs.next()){
                cli = new Cliente();
                cli.setCod_cliente(rs.getInt(1));
                cli.setNombre(rs.getString(2));
                cli.setApellido(rs.getString(3));
                cli.setRuc(rs.getInt(4));
                cli.setDireccion(rs.getString(5));
                cli.setRazon_social(rs.getString(6));
                
                clis.add(cli);                
            }            
        } catch (SQLException ex) {
            } finally {
            try {
                ps.close();
                rs.close();
                con.close();
            } catch (SQLException ex) {
                Logger.getLogger(CarniceriaDAO.class.getName()).log(Level.SEVERE, null, ex);
            }
            
        }
        return clis;
    }
    
    public List<Empleado> getEmpleados(){
        List<Empleado> emps = new ArrayList<>();
        Empleado emp = null;
        String sql = "select * from EMPLEADO";
        PreparedStatement ps = null;
            ResultSet rs = null;
               
        try { 
            con = dbc.getConnection();
            ps = con.prepareStatement(sql);
            rs = ps.executeQuery();
            while (rs.next()){
                emp = new Empleado();
                emp.setCod_empleado(rs.getInt(1));
                emp.setNombre(rs.getString(2));
                emp.setApellido(rs.getString(3));
                emp.setDni(rs.getInt(4));
                emps.add(emp);                
            }            
        } catch (SQLException ex) {
            } finally {
            try {
                ps.close();
                rs.close();
                con.close();
            } catch (SQLException ex) {
                Logger.getLogger(CarniceriaDAO.class.getName()).log(Level.SEVERE, null, ex);
            }
            
        }
        return emps;
    }
    
     public List<Proveedor> getProveedores(){
        List<Proveedor> provs = new ArrayList<>();
        Proveedor prov = null;
        String sql = "select * from PROVEEDOR";
        PreparedStatement ps = null;
            ResultSet rs = null;
               
        try { 
            con = dbc.getConnection();
            ps = con.prepareStatement(sql);
            rs = ps.executeQuery();
            while (rs.next()){
                prov = new Proveedor();
                prov.setCod_proveedor(rs.getInt(1));
                prov.setEmpresa(rs.getString(2));
                provs.add(prov);                
            }            
        } catch (SQLException ex) {
            } finally {
            try {
                ps.close();
                rs.close();
                con.close();
            } catch (SQLException ex) {
                Logger.getLogger(CarniceriaDAO.class.getName()).log(Level.SEVERE, null, ex);
            }
            
        }
        return provs;
    }
    
    
    public List<Comprobante> getVentasxFechas(Date fecha1,Date fecha2){
        List<Comprobante> comps = new ArrayList<>();
        Comprobante comp = null;
        PreparedStatement ps = null;
        ResultSet rs = null;
        String sql ="select * from comprobante where fecha_emision between to_date('?','dd/MM/yy') and to_date('?','dd/MM/yy')";
        try { 
            con = dbc.getConnection();
            ps = con.prepareStatement(sql);
            SimpleDateFormat df =  new SimpleDateFormat("dd/MM/yy");
            String fech1= df.format(fecha1);
            String fech2= df.format(fecha2);
            ps.setString(1, fech1);
            ps.setString(2, fech2);
            rs = ps.executeQuery();
            while (rs.next()){
                comp=new Comprobante();
                comp.setCod_venta(rs.getInt(1));
                comp.setCod_cliente(rs.getInt(2));
                comp.setPrecio_total(rs.getFloat(3));
                comp.setFecha_emision(rs.getDate(4));
                comp.setTipo(rs.getString(5));
                comps.add(comp);
            }            
        } catch (SQLException ex) {
            } finally {
            try {
                ps.close();
                rs.close();
                con.close();
            } catch (SQLException ex) {
                Logger.getLogger(CarniceriaDAO.class.getName()).log(Level.SEVERE, null, ex);
            }
            
        }
        
        return comps;
    }
    
    public List<String> calcularStocksxFechas(Date fecha1, Date fecha2 ){
        List<String> stocks = new ArrayList<>();
        String cad = "";
        PreparedStatement ps = null;
        ResultSet rs = null;
        String sql ="select p.descripcion, sum(d.cant_desperdicio), sum(costo_tot_desp)\n" +
"from desperdicio d join almacen a\n" +
"on d.cod_movimiento=a.cod_movimiento join producto p\n" +
"on a.cod_producto=p.cod_producto \n" +
"where fecha_caducidad between to_date('10/03/2009','dd/MM/yy') and sysdate\n" +
"group by p.descripcion";
        try { 
            con = dbc.getConnection();
            ps = con.prepareStatement(sql);
            SimpleDateFormat df =  new SimpleDateFormat("dd/MM/yy");
            String fech1= df.format(fecha1);
            String fech2= df.format(fecha2);
            ps.setString(1, fech1);
            ps.setString(2, fech2);
            rs = ps.executeQuery();
            while (rs.next()){
                cad=rs.getString(1)+"\t"+rs.getFloat(2)+"\t"+rs.getFloat(3);
                stocks.add(cad);
            }            
        } catch (SQLException ex) {
            } finally {
            try {
                ps.close();
                rs.close();
                con.close();
            } catch (SQLException ex) {
                Logger.getLogger(CarniceriaDAO.class.getName()).log(Level.SEVERE, null, ex);
            }
            
        }
        
        return stocks;
        
    }
    
    public List<Comprobante> getVentas(){
        List<Comprobante> comps = new ArrayList<>();
        Comprobante comp = null;
        String sql = "select * from COMPROBANTE";
        PreparedStatement ps = null;
        ResultSet rs = null;
        try { 
            con = dbc.getConnection();
            ps = con.prepareStatement(sql);
            rs = ps.executeQuery();
            while (rs.next()){
                comp=new Comprobante();
                comp.setCod_venta(rs.getInt(1));
                comp.setCod_cliente(rs.getInt(2));
                comp.setPrecio_total(rs.getFloat(3));
                comp.setFecha_emision(rs.getDate(4));
                comp.setTipo(rs.getString(5));
                comps.add(comp);
            }            
        } catch (SQLException ex) {
            } finally {
            try {
                ps.close();
                rs.close();
                con.close();
            } catch (SQLException ex) {
                Logger.getLogger(CarniceriaDAO.class.getName()).log(Level.SEVERE, null, ex);
            }
            
        }
        return comps;
    }
    
    public Producto getProducto (int cod){
        Producto prod = null;
        String sql = "select * from PRODUCTO where cod_producto = ?";
        PreparedStatement ps = null;
        ResultSet rs = null;
        
        
        try {
            con = dbc.getConnection();
            ps = con.prepareStatement(sql);
            ps.setInt(1, cod);
            rs = ps.executeQuery();
            if (rs.next()){
                prod = new Producto();
                prod.setCod_producto(cod);
                prod.setDescripcion(rs.getString(2));
                prod.setInt_vencimiento(rs.getInt(3));
                prod.setPrecio_unitario(rs.getFloat(4));
                
            }
            
        } catch (SQLException ex) {
            
        } finally {
            try {
                ps.close();
                rs.close();
                con.close();
            } catch (SQLException ex) {
                
            }
        }
        
        return prod;
    }
    
    public Cliente getCliente (int cod){
        Cliente cli = null;
        String sql = "select * from CLIENTE where cod_cliente = ?";
        PreparedStatement ps = null;
        ResultSet rs = null;
        
        
        try {
            con = dbc.getConnection();
            ps = con.prepareStatement(sql);
            ps.setInt(1, cod);
            rs = ps.executeQuery();
            if (rs.next()){
                cli = new Cliente();
                cli.setCod_cliente(cod);
                cli.setNombre(rs.getString(2));
                cli.setApellido(rs.getString(3));
                cli.setRuc(rs.getInt(4));
                cli.setDireccion(rs.getString(5));
                cli.setRazon_social(rs.getString(6));
                
            }
            
        } catch (SQLException ex) {
            Logger.getLogger(CarniceriaDAO.class.getName()).log(Level.SEVERE, null, ex);
        } finally {
            try {
                ps.close();
                rs.close();
                con.close();
            } catch (SQLException ex) {
                Logger.getLogger(CarniceriaDAO.class.getName()).log(Level.SEVERE, null, ex);
            }
        }
        
        return cli;
    }
    
    public Empleado getEmpleado (int cod){
        Empleado emp = null;
        String sql = "select * from EMPLEADO where cod_empleado = ?";
        PreparedStatement ps = null;
        ResultSet rs = null;
        
        
        try {
            ps = con.prepareStatement(sql);
            ps.setInt(1, cod);
            rs = ps.executeQuery();
            if (rs.next()){
                emp = new Empleado();
                emp.setCod_empleado(cod);
                emp.setNombre(rs.getString(2));
                emp.setApellido(rs.getString(3));
                emp.setDni(rs.getInt(4));
                
            }
            
        } catch (SQLException ex) {
            Logger.getLogger(CarniceriaDAO.class.getName()).log(Level.SEVERE, null, ex);
        } finally {
            try {
                ps.close();
                rs.close();
                con.close();
            } catch (SQLException ex) {
                Logger.getLogger(CarniceriaDAO.class.getName()).log(Level.SEVERE, null, ex);
            }
        }
        
        return emp;
    }
    
    public Proveedor getProveedor (int cod){
        Proveedor prov = null;
        String sql = "select * from PROVEEDOR where cod_proveedor = ?";
        PreparedStatement ps = null;
        ResultSet rs = null;
        
        
        try {
            ps = con.prepareStatement(sql);
            ps.setInt(1, cod);
            rs = ps.executeQuery();
            if (rs.next()){
                prov = new Proveedor();
                prov.setCod_proveedor(cod);
                prov.setEmpresa(rs.getString(2));
            }
            
        } catch (SQLException ex) {
            Logger.getLogger(CarniceriaDAO.class.getName()).log(Level.SEVERE, null, ex);
        } finally {
            try {
                ps.close();
                rs.close();
                con.close();
            } catch (SQLException ex) {
                Logger.getLogger(CarniceriaDAO.class.getName()).log(Level.SEVERE, null, ex);
            }
        }
        
        return prov;
    }
}
